/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbclasses;

import classes.Booking;
import java.sql.*;
import javax.swing.JOptionPane;

/**
 *
 */
public class BookingDB {
    private static Connection conn = null;
    private static PreparedStatement prest = null;
    
    /**
     * Method to insert the new booking into the database
     * @param newBooking the new booking object to be inserted
     * @return the success of the insert execution if true or false
     */
    public static boolean insertNewBooking(Booking newBooking) {
        
        //create the SQL statement for inserting a new booking into the database
        String query = "INSERT INTO booking (car,customer,start_date,return_date) VALUES (?,?,?,?)";
        boolean success = false;
        /*
         * Create a new connection, execute the insert statement putting the booking into the database.
         */
       
        conn = Dbconnector.getConnected();
        if(conn!=null){
            try {
                //execute the insert statement filling each column in the table
                prest = conn.prepareStatement(query);
                prest.setString(1, newBooking.getCar().getReg_no());
                prest.setString(2, newBooking.getCustomer().getCreditCard().getCardNumber());
                prest.setDate(3, newBooking.getStartDate());
                prest.setDate(4, newBooking.getReturnDate());
                prest.executeUpdate();
                conn.close();
                success = true;
            
            } catch (SQLException ex) {
                //tell the client which method call errored out and what was the message
                JOptionPane.showMessageDialog(null, "Problem in insertNewBooking " +ex.getSQLState());
            }
        }
        return success;
    }
    
    /**
     * Method to get the booking ID of the new booking from the database
     * @param newBooking the booking which will form the query
     * @return the booking ID
     */
    public static int getBookingId(Booking newBooking) {
        int bookingId = 0;
        
        conn = Dbconnector.getConnected();
        
        if(conn!=null){
            try {
                try (Statement st = conn.createStatement()) {
                    
                    //query the database for the booking ID based on the car regno and start date
                    ResultSet rs = st.executeQuery("Select booking_no FROM booking WHERE car='"+newBooking.getCar().getReg_no()+"'"
                            + " AND start_date ='"+newBooking.getStartDate()+"';");
                    while (rs.next()) {
                        bookingId = rs.getInt("booking_no");
                    }
                }
                conn.close();
            
            } catch (SQLException ex) {
                //tell the client which method call errored out and what was the message
                JOptionPane.showMessageDialog(null, "Problem in getBookingId " +ex.getSQLState());
            }
        }
        //returning the booking ID
        return bookingId;
    }
}